package ch.reevolt.curchod.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ch.reevolt.curchod.ui.ConnectionPane;

import data.Employee;
import data.Tool;

public class DataBase {

	/**
	 * singleton implementation
	 * 
	 */
	private static DataBase _instance = new DataBase();

	public static DataBase getInstance() {
		return _instance;
	}

	/**
	 * database interaction
	 */

	private String database = "curchod";
	public String username = "root";
	public String password = "root";

	private boolean isConnected = false;

	private String url = "jdbc:mysql://localhost:3306/" + database;

	/**
	 * SQL table informations
	 */
	private final String TABLE_EMPLOYEES = "employees";
	private final String TABLE_TOOLS = "tools";
	private final String TABLE_TRANSACTIONS = "transactions";

	private Connection connection = null;
	private java.sql.Statement statement = null;
	private ResultSet result = null;

	public boolean connect() {
		try {
			connection = DriverManager.getConnection(url, username, password);
			statement = connection.createStatement();
			isConnected = true;
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public void closeConnection() {
		try {
			connection.close();
			isConnected = false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean isConnected() {
		return isConnected;
	}

	private ResultSet executeQuery(String query) {
		if (!isConnected()) {
			new ConnectionPane();
		}
		try {
			System.out.println(query);
			if (query.split(" ")[0].compareToIgnoreCase("insert") == 0
					|| query.split(" ")[0].compareToIgnoreCase("update") == 0) {
				statement.executeUpdate(query);
			} else
				statement.executeQuery(query);
			return statement.getResultSet();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Employee management
	 */
	public Employee getEmployee(int barCode) {
		String name = "";
		String surname = "";
		int id = 0;
		try {
			result = executeQuery("SELECT name, surname, id, code FROM " + TABLE_EMPLOYEES + " WHERE code=" + barCode);
			while (result.next()) {
				name = result.getString("name");
				surname = result.getString("surname");
				id = result.getInt("code");
			}
			return new Employee(id, name, surname);

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public void addEmployee(int code, String name, String surname) {
		result = executeQuery("INSERT INTO " + TABLE_EMPLOYEES + "( code, name, surname) VALUES (" + code + ", '" + name
				+ "', '" + surname + "')");
	}

	/**
	 * Tools management
	 */
	public Tool getTool(int code) {
		String name = "";
		String description = "";
		int id = 0;
		try {
			result = executeQuery("SELECT name, description, code FROM " + TABLE_TOOLS + " WHERE code=" + code);
			while (result.next()) {
				name = result.getString("name");
				description = result.getString("description");
				id = result.getInt("code");
			}
			return new Tool(id, name, description);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	public void addTool(int id, String name, String description) {
		result = executeQuery("INSERT INTO " + TABLE_TOOLS + " ( code, name , description, position) VALUES (" + id
				+ ", \"" + name + "\", \"" + description + "\", 1)");
	}

	/**
	 * transaction
	 */
	public boolean output(Employee employee, Tool tool) {
		result = executeQuery("INSERT INTO transactions (`employee_code`, `tool_code`, `in_out`, `when`) VALUES ("
				+ employee.id() + ", " + tool.id() + ", '0', NOW())");

		executeQuery("UPDATE " + TABLE_TOOLS + " SET position = 0 WHERE code=" + tool.id());

		return true;
	}

	public boolean input(Employee employee, Tool tool) {
		result = executeQuery("INSERT INTO " + TABLE_TRANSACTIONS
				+ " ( employee_code, tool_code, `in_out`, `when` ) VALUES (" + employee.id() + ", " + tool.id()
				+ ", '1', NOW())");

		executeQuery("UPDATE " + TABLE_TOOLS + " SET position = 1 WHERE code=" + tool.id());

		return true;
	}

	public String[][] getHistoric(int code) {

		ArrayList<ArrayList<String>> retVal = new ArrayList<ArrayList<String>>();
		ArrayList<String> content;

		String query = "";

		if (code == 0 || code == 1) {

			query = "SELECT " + TABLE_EMPLOYEES + ".name as ename, " + TABLE_EMPLOYEES + ".surname as esurname, "
					+ TABLE_TOOLS + ".name as tname, " + TABLE_TOOLS + ".description as tdescription, " + TABLE_TRANSACTIONS
					+ ".when, " + TABLE_TRANSACTIONS + ".in_out FROM " + TABLE_TRANSACTIONS
					+ " LEFT JOIN employees ON transactions.employee_code = " + TABLE_EMPLOYEES + ".code  LEFT JOIN tools ON "
					+ TABLE_TRANSACTIONS + ".tool_code = tools.code WHERE " + TABLE_TRANSACTIONS + ".in_out = " + code;
			result = executeQuery(query);

			try {
				while (result.next()) {
					content = new ArrayList<String>();

					content.add(result.getString("ename"));
					content.add(result.getString("esurname"));
					content.add(result.getString("tname"));
					content.add(result.getString("tdescription"));
					if (result.getInt("in_out") == 0)
						content.add("Sortie");
					else
						content.add("Entrée");
					content.add(result.getString("when"));

					retVal.add(content);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		else if (code == 2 || code == 3) {

			// tool in stock, get each of them
			if (code == 3) {
				query = "SELECT tools.name AS tname, " + "tools.description AS tdescription " + "FROM tools "
						+ "WHERE tools.position = 1";

				result = executeQuery(query);

				try {
					while (result.next()) {
						content = new ArrayList<String>();

						content.add(result.getString("tname"));
						content.add(result.getString("tdescription"));
						content.add("");
						content.add("");
						content.add("");
						content.add("");

						retVal.add(content);

					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

			else if (code == 2) {

				query = "SELECT max(tr1.when), tls.name as tname, tls.description as tdescription, em.name as ename, em.surname as esurname ,  `when`"
						+ " FROM "
						+ TABLE_TRANSACTIONS
						+ " tr1, "
						+ TABLE_EMPLOYEES
						+ " em, "
						+ TABLE_TOOLS
						+ " tls WHERE em.code = tr1.employee_code "
						+ "AND  tls.code = tr1.tool_code AND tls.position = 0 GROUP BY tr1.tool_code";

				result = executeQuery(query);

				try {
					while (result.next()) {
						content = new ArrayList<String>();

						content.add(result.getString("ename"));
						content.add(result.getString("esurname"));
						content.add(result.getString("tname"));
						content.add(result.getString("tdescription"));
						content.add(result.getString("when"));
						content.add("");

						retVal.add(content);

					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

		}

		// it's a tool
		else if (code % 2 == 0) {
			query = "SELECT " + TABLE_EMPLOYEES + ".name as ename, " + TABLE_EMPLOYEES + ".surname as esurname, when, " + TABLE_TRANSACTIONS + ".in_out FROM " + TABLE_TRANSACTIONS
					+ " LEFT JOIN employees ON transactions.employee_code = " + TABLE_EMPLOYEES + ".code  LEFT JOIN tools ON "
					+ TABLE_TRANSACTIONS + ".tool_code = tools.code WHERE " + TABLE_TRANSACTIONS + ".tool_code = " + code;
			result = executeQuery(query);

			try {
				while (result.next()) {
					content = new ArrayList<String>();

					content.add(result.getString("ename"));
					content.add(result.getString("esurname"));

					if (result.getInt("in_out") == 0)
						content.add("Sortie");
					else
						content.add("Entrée");

					content.add(result.getString("when"));

					content.add("");
					content.add("");

					retVal.add(content);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		// it's an employee
		else if (code % 2 != 0) {
			query = "SELECT " + TABLE_TOOLS + ".name as tname, " + TABLE_TOOLS + ".description as tdescription, "
					+ TABLE_TRANSACTIONS + ".when, " + TABLE_TRANSACTIONS + ".in_out FROM " + TABLE_TRANSACTIONS
					+ " LEFT JOIN employees ON transactions.employee_code = " + TABLE_EMPLOYEES + ".code  LEFT JOIN tools ON "
					+ TABLE_TRANSACTIONS + ".tool_code = tools.code WHERE " + TABLE_TRANSACTIONS + ".employee_code = " + code;
			result = executeQuery(query);

			try {
				while (result.next()) {
					content = new ArrayList<String>();

					content.add(result.getString("tname"));
					content.add(result.getString("tdescription"));

					if (result.getInt("in_out") == 0)
						content.add("Sortie");
					else
						content.add("Entrée");

					retVal.add(content);

					content.add(result.getString("when"));

					content.add("");
					content.add("");

				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		if (retVal.size() == 0) {
			return null;
		} else {
			String[][] finalArray = new String[retVal.size()][retVal.get(0).size()];
			for (int j = 0; j < retVal.size(); j++) {
				for (int i = 0; i < retVal.get(0).size(); i++) {
					finalArray[j][i] = retVal.get(j).get(i);
				}
			}
			return finalArray;
		}
	}

	public String[][] getHistoric() {

		ArrayList<ArrayList<String>> retVal = new ArrayList<ArrayList<String>>();
		ArrayList<String> content;

		String query = "";

		query = " SELECT " + TABLE_TOOLS + ".name as tname, " + TABLE_TOOLS + ".description as tdescription, "
				+ TABLE_EMPLOYEES + ".name as ename, " + TABLE_EMPLOYEES + ".surname as esurname, " + TABLE_TRANSACTIONS
				+ ".when, " + TABLE_TRANSACTIONS + ".in_out as in_out FROM " + TABLE_TRANSACTIONS + " LEFT JOIN "
				+ TABLE_EMPLOYEES + " ON " + TABLE_TRANSACTIONS + ".employee_code = " + TABLE_EMPLOYEES + ".code LEFT JOIN "
				+ TABLE_TOOLS + " ON " + TABLE_TRANSACTIONS + ".tool_code = " + TABLE_TOOLS + ".code";
		result = executeQuery(query);

		try {
			while (result.next()) {
				content = new ArrayList<String>();

				content.add(result.getString("ename"));
				content.add(result.getString("esurname"));
				content.add(result.getString("tname"));
				content.add(result.getString("tdescription"));
				if (result.getInt("in_out") == 0)
					content.add("Sortie");
				else
					content.add("Entrée");
				content.add(result.getString("when"));

				retVal.add(content);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String[][] finalArray = new String[retVal.size()][retVal.get(0).size()];

		for (int j = 0; j < retVal.size(); j++) {
			for (int i = 0; i < retVal.get(0).size(); i++) {
				finalArray[j][i] = retVal.get(j).get(i);
				System.out.print(finalArray[j][i] + " - ");
			}
			System.out.println();
		}

		return finalArray;
	}
}
